﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.Iso27001;
using IsmsExplorer.Infrastructure.RepositoryFramework;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using System.Data;
using System.Data.Common;
using IsmsExplorer.Infrastructure.EntityFactoryFramework;
using IsmsExplorer.Infrastructure.Helper;
using IsmsExplorer.Infrastructure.Repositories.Documents;

namespace IsmsExplorer.Infrastructure.Repositories.Iso27001 {
    /// <summary>
    /// This Repository is asset special case as we simply want to read from it (no write/update as ISO Controls
    /// are immutable). At the same time we want to be able to load the complete hierarchy of objects.
    /// e.g. when loading asset section to load the subsections and for each subsection the controls.
    /// Hence we don'doc inherit the SqlServerRepositoryBase, rather go asset level up to RepositoryBase.
    /// </summary>
    internal class Iso27001ControlRepository : RepositoryBase<Iso27001ControlBase>, IIso27001ControlRepository {
        #region Data members
        Database _db;
        IEntityFactory<Iso27001ControlSection> _sectionFactory;
        IEntityFactory<Iso27001ControlSubsection> _subsectionFactory;
        IEntityFactory<Iso27001Control> _controlFactory;
        #endregion

        #region Construction
        // No UnitOfWork required as we're not modifying data
        public Iso27001ControlRepository() : this(null) {
        }

        public Iso27001ControlRepository(IUnitOfWork unitOfWork) : base(unitOfWork) {
            // No argument call uses the default database.
            // Might want to configure asset named instance.
            _db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
            _sectionFactory = EntityFactoryBuilder<Iso27001ControlSection>.BuildFactory<Iso27001ControlSection>();
            _subsectionFactory = EntityFactoryBuilder<Iso27001ControlSubsection>.BuildFactory<Iso27001ControlSubsection>();
            _controlFactory = EntityFactoryBuilder<Iso27001Control>.BuildFactory<Iso27001Control>();

        }
        #endregion

        public override Iso27001ControlBase FindBy(object key) {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Not implemented. If caller requires all control hierarchy, should call FindAllSections(recursive: true);
        /// </summary>
        /// <returns></returns>
        public override IList<Iso27001ControlBase> FindAll() {
            throw new NotImplementedException();
        }

        #region Persistence (NOT implemented as it's not required (ISO Controls are fixed)
        protected override void PersistNewItem(Iso27001ControlBase item) {
            throw new NotImplementedException();
        }

        protected override void PersistUpdatedItem(Iso27001ControlBase item) {
            throw new NotImplementedException();
        }

        protected override void PersistDeletedItem(Iso27001ControlBase item) {
            throw new NotImplementedException();
        }
        #endregion

        protected IDataReader ExecuteReader(string sql) {
            DbCommand command = _db.GetSqlStringCommand(sql);
            return _db.ExecuteReader(command);
        }

        protected virtual T BuildEntityFromReader<T>(IDataReader reader) where T : Iso27001ControlBase {
            IEntityFactory<T> _entityFactory = EntityFactoryBuilder<T>.BuildFactory<T>();
            return _entityFactory.BuildEntity(reader);
        }

        protected virtual T BuildEntityFromSql<T>(string sql) where T : Iso27001ControlBase {
            T entity = default(T);
            using (IDataReader reader = this.ExecuteReader(sql)) {
                if (reader.Read()) {
                    entity = this.BuildEntityFromReader<T>(reader);
                }
            }
            return entity;
        }

        protected virtual IList<T> BuildEntitiesFromSql<T>(string sql) where T : Iso27001ControlBase {
            List<T> entities = new List<T>();
            using (IDataReader reader = this.ExecuteReader(sql)) {
                while (reader.Read()) {
                    entities.Add(this.BuildEntityFromReader<T>(reader));
                }
            }
            return entities;
        }

        public IList<Iso27001ControlSection> FindAllSections(bool recursive) {
            IList<Iso27001ControlSection> sections;
            // Get all sections.
            string sql = string.Format(@"SELECT {0},{1},{2} FROM [Iso27001ControlSections] ICS",
                Iso27001ControlSectionFactory.FieldNames.ID,
                Iso27001ControlSectionFactory.FieldNames.ReferenceID,
                Iso27001ControlSectionFactory.FieldNames.Name);
            sections = this.BuildEntitiesFromSql<Iso27001ControlSection>(sql);
            if (recursive) {
                foreach (Iso27001ControlSection section in sections) {
                    section.Subsections = FindAllSubsections(section, recursive);
                }
            }
            return sections;
        }

        public IList<Iso27001ControlSubsection> FindAllSubsections(Iso27001ControlSection section, bool recursive) {
            IList<Iso27001ControlSubsection> subsections;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT {0},{1},{2},{3} FROM [Iso27001ControlSubsections] Sub",
                Iso27001ControlSubsectionFactory.FieldNames.ID,
                Iso27001ControlSubsectionFactory.FieldNames.ReferenceID,
                Iso27001ControlSubsectionFactory.FieldNames.Name,
                Iso27001ControlSubsectionFactory.FieldNames.Objective);
            if (section != null) {
                sb.AppendFormat(@" WHERE Sub.{0}={1}", Iso27001ControlSubsectionFactory.FieldNames.SectionID, DataHelper.GetSqlValue(section.ID));
            }
            subsections = this.BuildEntitiesFromSql<Iso27001ControlSubsection>(sb.ToString());
            if (recursive) {
                foreach (Iso27001ControlSubsection  subsection in subsections) {
                    subsection.Controls = FindAllControls(subsection);
                }
            }
            return subsections;
        }

        public IList<Iso27001Control> FindAllControls(Iso27001ControlSubsection subsection) {
            IList<Iso27001Control> controls;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT {0},{1},{2},{3} FROM [Iso27001Controls] C",
                Iso27001ControlFactory.FieldNames.ID,
                Iso27001ControlFactory.FieldNames.ReferenceID,
                Iso27001ControlFactory.FieldNames.Name,
                Iso27001ControlFactory.FieldNames.Description);
            if (subsection != null) {
                sb.AppendFormat(@" WHERE C.{0}={1}", Iso27001ControlFactory.FieldNames.SubsectionID, DataHelper.GetSqlValue(subsection.ID));
            }
            controls = this.BuildEntitiesFromSql<Iso27001Control>(sb.ToString());
            return controls;
        }

        public Iso27001Control FindControlByID(int controlID) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT {0},{1},{2},{3} FROM [Iso27001Controls] WHERE {4}={5}",
                Iso27001ControlFactory.FieldNames.ID,
                Iso27001ControlFactory.FieldNames.ReferenceID,
                Iso27001ControlFactory.FieldNames.Name,
                Iso27001ControlFactory.FieldNames.Description,
                Iso27001ControlFactory.FieldNames.ID,
                DataHelper.GetSqlValue(controlID)
                );
            return this.BuildEntityFromSql<Iso27001Control>(sb.ToString());
        }

        public Iso27001Control FindControlByReferenceID(string referenceID) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT {0},{1},{2},{3} FROM [Iso27001Controls] WHERE {4}={5}",
                Iso27001ControlFactory.FieldNames.ID,
                Iso27001ControlFactory.FieldNames.ReferenceID,
                Iso27001ControlFactory.FieldNames.Name,
                Iso27001ControlFactory.FieldNames.Description,
                Iso27001ControlFactory.FieldNames.ReferenceID,
                DataHelper.GetSqlValue(referenceID)
                );
            return this.BuildEntityFromSql<Iso27001Control>(sb.ToString());
        }

        public IList<Iso27001Control> FindAllControlsForDocument(int projectID, object documentID) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT {0},{1},{2},{3} FROM [Iso27001Controls] WHERE {4} IN",
                Iso27001ControlFactory.FieldNames.ID,
                Iso27001ControlFactory.FieldNames.ReferenceID,
                Iso27001ControlFactory.FieldNames.Name,
                Iso27001ControlFactory.FieldNames.Description,
                Iso27001ControlFactory.FieldNames.ID);
            sb.AppendFormat(@"(SELECT DISTINCT {0} FROM [Iso27001ControlDocuments] WHERE {1}={2} AND {3}={4}) ORDER BY {5}",
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
                DataHelper.GetSqlValue(projectID),
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID,
                DataHelper.GetSqlValue(documentID),
                Iso27001ControlFactory.FieldNames.ID
                );
            return this.BuildEntitiesFromSql<Iso27001Control>(sb.ToString());
        }

    } // end internal class Iso27001ControlRepository : RepositoryBase<Iso27001ControlSection>, IIso27001ControlRepository
}
